Option Explicit On

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Text

Public Class Lookups

#Region "Products"

    Public Shared Function ProductTypes() As DataTable

        Dim SQL As New StringBuilder

        With SQL
            .Append("select producttype_id, producttype_name from l_producttypes ")
            .Append("order by producttype_departmentid, producttype_name")
        End With

        Return Data.GetQueryResults(SQL.ToString)

    End Function

    Public Shared Function UKVisas() As DataTable
        Return Data.GetQueryResults("Select UKVisa_ID,UKVisa_Name from L_UKVisa Order By UKVisa_Name", True)
    End Function

#End Region


#Region "Consultants"

    Public Shared Function ConsultantList() As DataTable

        Dim SQL As New StringBuilder

        SQL.Append("select con_id,con_name from l_consultant where con_status = 1 order by con_name")
        Return Data.GetQueryResults(SQL.ToString)

    End Function


    Public Shared Function ConsultantListBranchWise(ByVal conId As Integer) As DataTable

        Dim SQL As New StringBuilder

        'SQL.Append("exec SP_ConsultantList " & conId)
        SQL.Append("select con_id,con_name from l_consultant where con_status = 1 and con_branchid=(select con_branchid from l_consultant where con_id=" & conId & ") order by con_name ")

        Return Data.GetQueryResults(SQL.ToString)

    End Function

    Public Shared Function ConsultantName(ByVal ConsultantId As Integer) As String

        Dim SQL As New StringBuilder

        SQL.Append("select con_name from l_consultant where con_id = " & ConsultantId)
        Return Data.GetScalar(SQL.ToString)

    End Function

    Public Shared Function DepartmentList(Optional ByVal ProductsOnly = False) As DataTable

        Dim SQL As New StringBuilder

        With SQL
            .Append("select department_id,department_name from l_departments ")
            If ProductsOnly Then
                .Append("where department_products = 1 ")
            End If
            .Append("order by department_name")
        End With

        Return Data.GetQueryResults(SQL.ToString)

    End Function
    Public Shared Function BranchList(Optional ByVal ProductsOnly = False) As DataTable

        Dim SQL As New StringBuilder

        With SQL
            .Append("select branch_id,branch_name from l_branch ")
            'If ProductsOnly Then
            '.Append("where department_products = 1 ")
            'End If
            .Append("order by branch_name")
        End With

        Return Data.GetQueryResults(SQL.ToString)

    End Function

    Public Shared Function BranchList(ByVal conId As Integer) As DataTable

        Dim SQL As New StringBuilder

        With SQL
            '.Append("exec SP_BranchList " & conId)
            .Append("select branch_id,branch_name from l_branch ")
            'If ProductsOnly Then
            '.Append("where department_products = 1 ")
            'End If
            .Append("order by branch_name")
        End With

        Return Data.GetQueryResults(SQL.ToString)

    End Function

    Public Shared Function AccountManagers() As DataTable

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT Con_ID, Con_Name, Con_Department ")
            .Append("FROM L_Consultant ")
            .Append("WHERE Con_Status = 1 ORDER BY Con_Name")
        End With

        Return Data.GetQueryResults(SQL.ToString, True)

    End Function

#End Region

    Public Shared Function TitleList() As DataTable

        Return Data.GetQueryResults("Select title_id, Title_Text From L_Titles Order By Title_Text", True)

    End Function


    Public Shared Function WhereHeard() As DataTable

        Return Data.GetQueryResults("Select wherehear_id, WhereHear_Text From L_WhereHear Order By WhereHear_Text", True)

    End Function

    Public Shared Function CountyList() As DataTable

        Return Data.GetQueryResults("select county_id, county_name From l_county Order By county_name", True)

    End Function

    Public Shared Function CountryList(Optional ByVal Nationality As Boolean = False, Optional ByVal Destination As Integer = 0)

        Dim SQL As New StringBuilder

        With SQL
            .Append("select country_id, ")
            If Not Nationality Then
                .Append("country_name ")
            Else
                .Append("country_nationality ")
            End If
            .Append("from l_country ")
            If Destination > 0 Then
                .Append("where country_destination = " & Destination)
            End If
            If Not Nationality Then
                .Append(" order by country_name")
            Else
                .Append(" order by country_nationality")
            End If
        End With

        Return Data.GetQueryResults(SQL.ToString, True)

    End Function

    
#Region "Employment"

    Public Shared Function JobRoles() As DataTable
        Return Data.GetQueryResults("SELECT Role_ID,Role_Name,Role_Employee FROM L_Roles ORDER BY Role_Name", True)
    End Function

    Public Shared Function Qualifications() As DataTable
        Return Data.GetQueryResults("Select Qual_Id, Qual_Name from L_Qualifications Order By Qual_Name", True)
    End Function

    Public Shared Function SkilledCategory() As DataTable
        Return Data.GetQueryResults("SELECT SkilledCategory_ID,SkilledCategory_Name FROM L_SkilledCategories ORDER BY SkilledCategory_Name", True)
    End Function

    Public Shared Function SkillOccupations(ByVal CategoryId As Integer) As DataTable

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT SkilledOcc_ID, SkilledOcc_Title FROM L_SkilledOcc ")
            .Append("WHERE SkilledOcc_CategoryID = " & CategoryId & " ")
            .Append("AND SkilledOcc_Inactive = 0 ")
            .Append("ORDER BY SkilledOcc_Title")
            Return Data.GetQueryResults(.ToString, True)
        End With

    End Function


#End Region


End Class
